﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace QLHXNK.DataAccess
{
    class Monthly_ExpensesData
    {
        #region --------------------------------------------Thêm, Xoá, Sửa-----------------------------------------
        //Thêm:
        public static int Insert(String month_name, String expenses_id, String amount, String remark)
        {
            String strSQL = String.Format("INSERT INTO Monthly_Expenses (month_name, expenses_id, amount, remark) VALUES ('{0}', '{1}', {2},'{3}')", month_name.Replace("'", "''"), expenses_id.Replace("'", "''"), amount, remark.Replace("'", "''"));
            return MyDataTransfer.executeNonQueryCommand(strSQL);
        }
        #region Kiểm tra tồn tại
        public static int Existed(String monthName, String expensesID)
        {
            DataTable table = MyDataTransfer.executeQueryCommand(String.Format("SELECT ID FROM Monthly_Expenses WHERE month_name = '{0}' AND expenses_id = {1}", monthName, expensesID));
            if (table != null && table.Rows.Count > 0)
            {
                try
                {
                    return (int)table.Rows[0]["ID"];
                }
                catch
                {
                    return -1;
                }
            }
            return -1;
        }

        #endregion
        //Xóa:
        public static int Delete(int ID)
        {
            String strSQL = String.Format("DELETE FROM Monthly_Expenses WHERE ID = {0}", ID);
            return MyDataTransfer.executeNonQueryCommand(strSQL);
        }
        public static int Delete(String monthName)
        {
            String strSQL = String.Format("DELETE FROM Monthly_Expenses WHERE month_name = '{0}'", monthName);
            return MyDataTransfer.executeNonQueryCommand(strSQL);
        }
        //Sửa:
        public static int Update(int ID, String month_name, String expenses_id, String amount, String remark)
        {
            String strSQL = String.Format("UPDATE  Monthly_Expenses SET month_name = '{0}', expenses_id = '{1}', amount = {2},  remark = '{3}' WHERE ID = {4}", month_name.Replace("'", "''"), expenses_id.Replace("'", "''"), amount, remark.Replace("'", "''"), ID);

            return MyDataTransfer.executeNonQueryCommand(strSQL);
        }
        #endregion

        #region -----------------------------------------Lấy danh sách----------------------------------
        public static DataTable getMonthly_ExpensesList(String strSQL)
        {
            if (strSQL == "")
                strSQL = "SELECT * FROM Monthly_Expenses";
            return MyDataTransfer.executeQueryCommand(strSQL);
        }
        #endregion
    }
}
